﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using XeBus.BusinessObject;
using System.Data;
using System.Data.SqlClient;

namespace XeBus.DataAccess
{

    public class TuyenXeBusDAO
    {
        private ConnectData _conn;

        public TuyenXeBusDAO()
        {
            _conn = new ConnectData();
        }

        // Hàm thêm mới một tuyến xe
        public void THEM_TUYEN_XE(TuyenXeBuyt txb)
        {

            SqlParameter[] param = new SqlParameter[7];

            param[0] = new SqlParameter("P_SO_HIEU_TUYEN", SqlDbType.NVarChar);
            param[0].Value = txb.So_Hieu_Tuyen;

            param[1] = new SqlParameter("P_TEN_TUYEN", SqlDbType.NVarChar);
            param[1].Value = txb.Ten_Tuyen;

            param[2] = new SqlParameter("P_CHIEU_DAI_TUYEN", SqlDbType.Int);
            if (txb.Chieu_Dai_Tuyen.Length > 0)
                param[2].Value = txb.Chieu_Dai_Tuyen;
            else
                param[2].Value = DBNull.Value;

            param[3] = new SqlParameter("P_TG_BAT_DAU", SqlDbType.DateTime);
            if (txb.TG_Bat_Dau.Length > 0)
                param[3].Value = txb.TG_Bat_Dau;
            else
                param[3].Value = DBNull.Value;

            param[4] = new SqlParameter("P_TG_KET_THUC", SqlDbType.DateTime);
            if (txb.TG_Ket_Thuc.Length > 0)
                param[4].Value = txb.TG_Ket_Thuc;
            else
                param[4].Value = DBNull.Value;

            param[5] = new SqlParameter("P_TG_GIAN_CACH_CD", SqlDbType.Int);
            if (txb.TG_Gian_Cach_CD.Length > 0)
                param[5].Value = txb.TG_Gian_Cach_CD;
            else
                param[5].Value = DBNull.Value;

            param[6] = new SqlParameter("P_TG_GIAN_CACH_TD", SqlDbType.Int);
            if (txb.TG_Gian_Cach_TD.Length > 0)
                param[6].Value = txb.TG_Gian_Cach_TD;
            else
                param[6].Value = DBNull.Value;

            _conn.ExecuteNonQuery("THEM_TUYENXEBUYT", param);
        }

        public DataTable TuyenXeBuyt()
        {
            return _conn.ExecuteSelectQuery("SELECT *, RIGHT(CONVERT(VARCHAR, TG_Bat_Dau, 100),7) Bat_Dau,RIGHT(CONVERT(VARCHAR, TG_Ket_Thuc, 100),7) Ket_Thuc FROM TuyenXeBuyt", null);
        }

        public DataTable LayDuLieuTheoID(string id)
        {
            return _conn.ExecuteSelectQuery("SELECT *, RIGHT(CONVERT(VARCHAR, TG_Bat_Dau, 100),7) Bat_Dau,RIGHT(CONVERT(VARCHAR, TG_Ket_Thuc, 100),7) Ket_Thuc FROM TuyenXeBuyt WHERE Ma_Tuyen=" + id + ";", null);
        }

        public void SUA_TUYEN_XE(TuyenXeBuyt txb)
        {
            SqlParameter[] param = new SqlParameter[8];

            param[0] = new SqlParameter("P_SO_HIEU_TUYEN", SqlDbType.NVarChar);
            param[0].Value = txb.So_Hieu_Tuyen;

            param[1] = new SqlParameter("P_TEN_TUYEN", SqlDbType.NVarChar);
            param[1].Value = txb.Ten_Tuyen;

            param[2] = new SqlParameter("P_CHIEU_DAI_TUYEN", SqlDbType.Int);
            if (txb.Chieu_Dai_Tuyen.Length > 0)
                param[2].Value = txb.Chieu_Dai_Tuyen;
            else
                param[2].Value = DBNull.Value;

            param[3] = new SqlParameter("P_TG_BAT_DAU", SqlDbType.DateTime);
            if (txb.TG_Bat_Dau.Length > 0)
                param[3].Value = txb.TG_Bat_Dau;
            else
                param[3].Value = DBNull.Value;

            param[4] = new SqlParameter("P_TG_KET_THUC", SqlDbType.DateTime);
            if (txb.TG_Ket_Thuc.Length > 0)
                param[4].Value = txb.TG_Ket_Thuc;
            else
                param[4].Value = DBNull.Value;

            param[5] = new SqlParameter("P_TG_GIAN_CACH_CD", SqlDbType.Int);
            if (txb.TG_Gian_Cach_CD.Length > 0)
                param[5].Value = txb.TG_Gian_Cach_CD;
            else
                param[5].Value = DBNull.Value;

            param[6] = new SqlParameter("P_TG_GIAN_CACH_TD", SqlDbType.Int);
            if (txb.TG_Gian_Cach_TD.Length > 0)
                param[6].Value = txb.TG_Gian_Cach_TD;
            else
                param[6].Value = DBNull.Value;

            param[7] = new SqlParameter("P_MA_TUYEN", SqlDbType.Int);
            if (txb.Ma_Tuyen.Length > 0)
                param[7].Value = txb.Ma_Tuyen;
            else
                param[7].Value = DBNull.Value;

            _conn.ExecuteNonQuery("SUA_TUYENXEBUYT", param);
        }

        public void XOA(string id)
        {
            _conn.ExecuteNonQueryString("DELETE FROM TuyenXeBuyt WHERE Ma_Tuyen=" + id + ";");
        }
    }
}
